# -*- coding: utf-8 -*-
#test database link
import codecs
import pyodbc
import traceback
import os
os.environ['TDSDUMP'] = 'stdout'
SERVER = '10.0.108.107'
USERNAME = 'lims'
PASSWORD = 'QmPz123@me'
DATABASE = 'caiji'
def s1():
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker
    import urllib
    from sqlalchemy import create_engine
    from sqlalchemy.engine import URL
    connection_string = f"mssql+pymssql://{USERNAME}:{PASSWORD}@{SERVER}/{DATABASE}?charset=utf8"
    # connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})
    engine = create_engine(connection_string)
    with engine.connect() as conn:
        print(dir(conn))
        r=conn.exec_driver_sql("SELECT * from bj_syy")
        while True:
            one=r.fetchone()
            if not one:
                break
            print(one)
def s2():
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker
    import urllib
    from sqlalchemy import create_engine
    from sqlalchemy.engine import URL
    connection_string = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};ENCRYPT=yes;TrustServerCertificate=yes;UID={USERNAME};PWD={PASSWORD}'
    connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})
    engine = create_engine(connection_url)
    with engine.connect() as conn:
        print(dir(conn))
        r=conn.exec_driver_sql("SELECT * from bj_syy")
        while True:
            one=r.fetchone()
            if not one:
                break
            print(one)
def get_data():
    import pymssql
    # import _mssql
    cnn = pymssql.connect(SERVER, USERNAME,PASSWORD, DATABASE,tds_version="7.0",charset="CP936")#,port=1433,charset="CP936")
    # cnn=_mssql.connect(SERVER,USERNAME,PASSWORD)
    cursor = cnn.cursor()
    cursor.execute("select * from bj_syy")
    data = cursor.fetchall()
    datas=[]
    for i in data:
        istr = list(i)
        # ii = istr[2:-3]
        # lists=ii.split()
        # self.model.todos.append(ii)
        datas.append(istr)
    # self.model.todos=data
    # self.save()
    cnn.commit()
    cnn.close()
    return datas
def updateDb():
    connectionString = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};ENCRYPT=yes;TrustServerCertificate=yes;UID={USERNAME};PWD={PASSWORD}'
    conn = pyodbc.connect(connectionString)
    cmds=codecs.open("caiji.sql","r",'utf-8').read().split(";")
    cursor = conn.cursor()
    for cmd  in cmds:
        print(cmd)
        cursor.execute(cmd)
    conn.commit()
    cursor.close()
    conn.close()
def getSyy():
    connectionString = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};ENCRYPT=yes;TrustServerCertificate=yes;UID={USERNAME};PWD={PASSWORD}'
    conn = pyodbc.connect(connectionString)
    cmds=codecs.open("caiji.sql","r",'utf-8').read().split(";")
    cursor = conn.cursor()
    cursor.execute("select * from syy")
    row = cursor.fetchone()
    print('name:', row[1])          # access by column index
    cursor.close()
    conn.close()
if __name__=="__main__":    
    # updateDb()    
    # print(get_data())
    s2()
    # getSyy()
